package OtherMethod;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.util.List;

import XmlLoader.FGObjProcess;
import XmlLoader.FGObjectStructure;
import XmlLoader.FGObjectTrajectoryStructure;

public class SqlGenerater {
	
	private final String TABLE_NAME = "trajectory";
	private final String TRAJ_ID = "id_traj";
	private final String POINT_ID = "id_point";
	private final String LOCATION = "location";
	private List<FGObjectTrajectoryStructure> listTraj = null;
	
	public SqlGenerater(String pathXML, String pathQuery) {
		FGObjProcess fgObj = new FGObjProcess(pathXML);
		List<String> list_objIndex = fgObj.ACT004_getStringList(pathQuery);
    	this.listTraj = fgObj.ACT001_getListOfObj(list_objIndex);
	}
	
	public void ACT001_genInsertCommand(String fileResult, int startID, int range) {
		File file = new File(fileResult);
		BufferedWriter bufWriter = null;
		try {
			bufWriter = new BufferedWriter(
					new OutputStreamWriter(new FileOutputStream(file,false),"utf8"));
			int indexTraj = 0;
			while(indexTraj < this.listTraj.size()) {
				int indexPoint = 0;
				FGObjectTrajectoryStructure fobj = this.listTraj.get(indexTraj);
				while(indexPoint < fobj.GET002_nNumberOfPoint()) {
					FGObjectStructure point = fobj.GET003_elementIn_list_fGObject(indexPoint);
					int trajID = fobj.GET001_nobjNewNumIndex() + startID;
					int pointID = point.GET003_pointid();
					int pointX = point.GET001_centerReal().x+(int)(Math.random()*range-1);
					int pointY = point.GET001_centerReal().y+(int)(Math.random()*range-1);
					bufWriter.write("INSERT INTO " + this.TABLE_NAME + "(" + TRAJ_ID + ", " + POINT_ID + ", " + LOCATION + ")" + 
					    "VALUES(" + trajID + "," + pointID + "," + "ST_GeomFromText('POINT(" + pointX + " " + pointY + ")')" + ");\n");
					indexPoint = indexPoint + 1;
				}
				indexTraj = indexTraj + 1;
			}
			bufWriter.close();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		SqlGenerater sg = new SqlGenerater("D:\\research\\Proj1 cross road\\data2\\20130312_video_cross_road_cut\\trajectory_xml", "123.txt");
		int start = 1, end = 20;
		int index = start;
		while(index <= end) {
			sg.ACT001_genInsertCommand("QCommand" + index + ".txt", start * 10000, (start/3) + 3);
			index = index + 1;
		}
	}
}
